package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

const (
	USERNAME = "root"
	PASSWORD = "111111"
	NETWORK  = "tcp"
	SERVER   = "localhost"
	PORT     = 3306
	DATABASE = "hy_tts_web"
)

type Log struct {
	ID   int64          `db:"ind"`
	Msg  sql.NullString `db:"err_msg"` //由于在mysql的users表中name没有设置为NOT NULL,所以name可能为null,在查询过程中会返回nil，如果是string类型则无法接收nil,但sql.NullString则可以接收nil值
	Time sql.NullString `db:"err_time"`
	del  int            `db:"del_sign"`
}

func main() {
	dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
	DB, err := sql.Open("mysql", dsn)
	defer DB.Close()
	if err != nil {
		fmt.Printf("Open mysql failed,err:%v\n", err)
		return
	}
	DB.SetConnMaxLifetime(100 * time.Second) //最大连接周期，超过时间的连接就close
	DB.SetMaxOpenConns(100)                  //设置最大连接数
	DB.SetMaxIdleConns(16)                   //设置闲置连接数
	tx, err := DB.Begin()
	queryOne(tx)
	queryMulti(tx)
	insertData(tx)
	//updateData(tx)
	//deleteData(tx)
	tx.Commit()
	//tx.Rollback()

}

//查询单行
func queryOne(DB *sql.Tx) {
	log := new(Log)
	row := DB.QueryRow("select * from hy_tts_log where ind = ?", 27)
	if err := row.Scan(&log.ID, &log.Msg, &log.Time, &log.del); err != nil {
		fmt.Printf("scan failed, err:%v", err)
		return
	}
	fmt.Println(*log)
}

//查询多行
func queryMulti(DB *sql.Tx) {
	log := new(Log)
	rows, err := DB.Query("select * from hy_tts_log where ind > ?", 1)
	defer func() {
		if rows != nil {
			rows.Close()
		}
	}()
	if err != nil {
		fmt.Printf("Query failed,err:%v", err)
		return
	}
	for rows.Next() {
		err = rows.Scan(&log.ID, &log.Msg, &log.Time, &log.del)
		if err != nil {
			fmt.Printf("Scan failed,err:%v", err)
			return
		}
		fmt.Print(*log)
	}
	fmt.Println()
}

//插入数据
func insertData(DB *sql.Tx) {
	stmt, _ := DB.Prepare("insert INTO hy_tts_log(err_msg) values(?)")
	defer stmt.Close()
	result, err := stmt.Exec("xys")
	//result,err := DB.Exec("insert INTO hy_tts_log(err_msg) values(?)","YDZ")
	if err != nil {
		fmt.Printf("Insert failed,err:%v", err)
		return
	}
	lastInsertID, err := result.LastInsertId()
	if err != nil {
		fmt.Printf("Get lastInsertID failed,err:%v", err)
		return
	}
	fmt.Println("LastInsertID:", lastInsertID)
	rowsaffected, err := result.RowsAffected()
	if err != nil {
		fmt.Printf("Get RowsAffected failed,err:%v", err)
		return
	}
	fmt.Println("RowsAffected:", rowsaffected)
}

//更新数据
func updateData(DB *sql.Tx) {
	stmt, _ := DB.Prepare("UPDATE hy_tts_log set err_msg=? where ind=?")
	defer stmt.Close()
	result, err := stmt.Exec("30", 3)

	//result,err := DB.Exec("UPDATE hy_tts_log set err_msg=? where ind=?","30",3)
	if err != nil {
		fmt.Printf("Insert failed,err:%v", err)
		return
	}
	rowsaffected, err := result.RowsAffected()
	if err != nil {
		fmt.Printf("Get RowsAffected failed,err:%v", err)
		return
	}
	fmt.Println("RowsAffected:", rowsaffected)
}

//删除数据
func deleteData(DB *sql.Tx) {
	stmt, _ := DB.Prepare("delete from hy_tts_log where ind=?")
	defer stmt.Close()
	result, err := stmt.Exec(1)

	//result,err := DB.Exec("delete from hy_tts_log where ind=?",1)
	if err != nil {
		fmt.Printf("Insert failed,err:%v", err)
		return
	}
	rowsaffected, err := result.RowsAffected()
	if err != nil {
		fmt.Printf("Get RowsAffected failed,err:%v", err)
		return
	}
	fmt.Println("RowsAffected:", rowsaffected)
}
